import fileinput
import sys
import os

# 将项目根目录添加到Python路径
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from datetime import datetime,date
from typing import List,Optional

from engine.utility import MysqlUtility,symbolstr2list,listofstr2symbolstr

from engine.object import BarData, FactorData
from engine.assets import Bond, Asset
from engine.constant import CashFlowType, OffsetType,AssetType
# 使用相对导入
from .database import BaseDatabase
from sqlalchemy import text 

import pandas as pd
from loguru import logger
from database.peeweemodels import (InInfIdpBndInfoDi,
                        Factors,
                        S02BndIbsettlementquote,
                        S07BndCbondcf,
                        S07BndValuation,
                        S07BndBasicinfoRealtime,
                        dbpool)
import datetime
from peewee import Case, fn, JOIN, SQL




class MysqlDatabase(BaseDatabase):
    """Mysql数据库接口"""

    def __init__(self, dbname="mysql") -> None:
        """初始化MySQL数据库连接
        
        Args:
            dbname: 配置文件中的数据库配置名称
        """
        try:
            self.engine = MysqlUtility(dbname).engine
            self.db = dbpool
            logger.info("MySQL数据库接口初始化成功")
        except Exception as e:
            raise Exception(f"MySQL数据库接口初始化失败: {e}")


    def load_bar_data(
        self,
        symbol: str,
        asset_type: AssetType,
        offset: OffsetType,
        start: str,
        end: str
    ) -> List[BarData]:
        """
        从数据库加载K线数据
        
        Args:
            symbol: 符号，可以是多个符号，用','分割
            asset_type: 资产类型
            offset: 偏移类型
            start: 开始时间,YYYYMMDD
            end: 结束时间,YYYYMMDD
            
        Returns:
            List[BarData]: K线数据列表
        """
        
        # 检查并修复日期格式
        if not start.isdigit() or not end.isdigit():
            logger.error(f"日期格式错误，应为YYYYMMDD数字格式: start={start}, end={end}")
            # 这里可以添加日期格式转换的逻辑，但目前先返回错误
            return []
            
        symbol_list = symbolstr2list(symbol)
        
        try:
            # 转换日期格式为datetime对象
            start_date = datetime.datetime.strptime(start, '%Y%m%d')
            end_date = datetime.datetime.strptime(end, '%Y%m%d')
            
            with self.db.connection_context():
                # 使用Peewee ORM查询
                duration_case = Case(None, [
                    (S07BndValuation.evalutedyield == 0, 0.25)
                ], S07BndValuation.evalutedyield)
                query = (S07BndValuation
                    .select(
                        InInfIdpBndInfoDi.tradingcode,
                        S07BndValuation.tradingday,
                        S07BndValuation.netvaluation,
                        S07BndValuation.modiduration,
                        duration_case.alias('evalutedyield'),
                        fn.COALESCE(S02BndIbsettlementquote.parvalvol, 0) * 100.0)
                    .join(
                        S02BndIbsettlementquote, 
                        JOIN.LEFT_OUTER,
                        on=((S07BndValuation.secucode == S02BndIbsettlementquote.secucode) & 
                            (S07BndValuation.tradingday == S02BndIbsettlementquote.tradingday)))
                    .join(
                        InInfIdpBndInfoDi,
                        JOIN.INNER,
                        on=(S07BndValuation.secucode == InInfIdpBndInfoDi.secucode))
                    .where(
                        (InInfIdpBndInfoDi.tradingcode.in_(symbol_list)) &
                        (S07BndValuation.tradingday >= start_date) &
                        (S07BndValuation.tradingday <= end_date)))
                
                # 转换为DataFrame
                result = pd.DataFrame(query.dicts())
                
                if result.empty:
                    logger.info(f"找不到行情数据 {symbol}")
                    return []
                
                # 重命名列以匹配原始查询
                result.columns = ['tradingcode', 
                                'tradingday', 
                                'netvaluation', 
                                'modiduration',
                                'evalutedyield',
                                'vol',]
            
            logger.info(f"查询结果行数: {len(result)}")
            
            bar_data_list = []
            for _, row in result.iterrows():
                b = BarData(close_price=row['netvaluation'],
                            open_price=row['netvaluation'],
                            high_price=row['netvaluation'],
                            low_price=row['netvaluation'],
                            avg_price=row['netvaluation'],
                            open_interest=None,
                            offset=offset,
                            volume=row['vol'],
                            timestamp=row['tradingday'],
                            symbol=row['tradingcode'],
                            duration=row['modiduration'],
                            YTM=row['evalutedyield'],
                            asset_type=asset_type
                )
                bar_data_list.append(b)
            logger.info(f"加载K线数据行数: {len(bar_data_list)}")
            return bar_data_list
            
        except Exception as e:
            logger.error(f"加载K线数据失败: {e}")
            return []


    def construct_asset(self, symbol) -> List[Asset]:
        """
        从数据库加载债券基本信息，构建资产类

        Args:
            symbol: 符号，可以是多个符号，用','分割
        """
        assets = []
        symbol_list = symbolstr2list(symbol)
        
        try:
            with self.db.connection_context():
                # 基本信息和现金流查询
                
                query = (InInfIdpBndInfoDi
                    .select(
                        InInfIdpBndInfoDi.secuabbr,
                        InInfIdpBndInfoDi.secucategory,
                        InInfIdpBndInfoDi.tradingcode,
                        InInfIdpBndInfoDi.listingdate,
                        InInfIdpBndInfoDi.enddate,
                        S07BndBasicinfoRealtime.issueprice,
                        S07BndBasicinfoRealtime.couponrate,
                        S07BndBasicinfoRealtime.bondtypecode, #10 附息 20 贴现 30 零息
                        S07BndBasicinfoRealtime.parval,
                        S07BndCbondcf.paymentdate,
                        S07BndCbondcf.paymentinterest,
                        S07BndCbondcf.paymentparvalue
                        )
                    .join(
                        S07BndCbondcf,
                        JOIN.INNER,
                        on=(InInfIdpBndInfoDi.tradingcode == S07BndCbondcf.tradingcode))
                    .join(S07BndBasicinfoRealtime,
                        JOIN.INNER,
                        on=(InInfIdpBndInfoDi.secucode == S07BndBasicinfoRealtime.secucode))
                    .where(
                        (InInfIdpBndInfoDi.tradingcode.in_(symbol_list)) &
                        (S07BndCbondcf.isvalid == 1) &
                        (InInfIdpBndInfoDi.is_dbtr == 0) &
                        (S07BndCbondcf.windcode.endswith('.IB'))))
                
                # 转换为DataFrame
                result = pd.DataFrame(query.dicts())
            
            if result.empty:
                logger.info(f"找不到基本信息 {symbol}")
                return None
            else:
                basicdf = result[['tradingcode','secucategory','listingdate','enddate','secuabbr','parval','bondtypecode','issueprice','couponrate']].copy().drop_duplicates()
                cashdf = result[['tradingcode','paymentdate','paymentinterest','paymentparvalue']].copy()
                
                for _, row1 in basicdf.iterrows():
                    cash_list = []
                    cashdf_ = cashdf[cashdf['tradingcode'] == row1.get('tradingcode')]
                    
                    for _, row in cashdf_.iterrows():
                        # 付息
                        if row.get('paymentinterest') != 0:
                            cash_list.append((row.get('paymentdate'),
                                           row.get('paymentinterest'),
                                           CashFlowType.INTEREST))
                        # 还本
                        if row.get('paymentparvalue') != 0:
                            cash_list.append((row.get('paymentdate'),
                                           row.get('paymentparvalue'),
                                           CashFlowType.PRINCIPAL))
                        
                    b = Bond(name=row1.get('secuabbr'),
                             secucategory=row1.get('secucategory'),
                             symbol=row1.get('tradingcode'),
                             value_date=row1.get('listingdate'),
                             maturity_date=row1.get('enddate'),
                             explicit_cashflows=cash_list,
                             issue_price=row1.get('issueprice'),
                             coupon_rate=row1.get('couponrate'),
                             bond_type=int(row1.get('bondtypecode')),
                             par_value=row1.get('parval')
                            )
                    assets.append(b)
            return assets
            
        except Exception as e:
            logger.error(f"构建资产失败: {e}")
            return []
            
    def load_factor_data(self, symbol: str, 
                               start: str, 
                               end: str,
                               offset: OffsetType=OffsetType.DAY) -> List[FactorData]:
        """
        从数据库加载因子数据
        
        Args:
            symbol: 符号，可以是多个符号，用','分割
            start: 开始时间,YYYYMMDD
            end: 结束时间,YYYYMMDD
            offset: 周期类型
            
        Returns:
            List[FactorData]: 因子数据列表
        """
        # 检查日期格式
        if not start.isdigit() or not end.isdigit():
            logger.error(f"日期格式错误，应为YYYYMMDD数字格式: start={start}, end={end}")
            return []
            
        symbol_list = symbolstr2list(symbol)
        
        try:
            # 转换日期格式
            start_date = datetime.datetime.strptime(start, '%Y%m%d')
            end_date = datetime.datetime.strptime(end, '%Y%m%d')
            
            with self.db.connection_context():
                # 使用Peewee查询因子数据
                query = (Factors
                    .select(Factors.tradingcode, 
                           Factors.tradingday, 
                           Factors.factor_value, 
                           Factors.factor_name)
                    .where(
                        (Factors.tradingcode.in_(symbol_list)) &
                        (Factors.tradingday.between(start_date, end_date))))
                
                # 转换为DataFrame
                result = pd.DataFrame(query.dicts())
            
            logger.info(f"条目记录数：{len(result)}")
            
            if result.empty:
                logger.info(f"找不到因子数据 {symbol}, {start} ~ {end}")
                return None
            else:
                factor_data = []
                for tradingday_ in result.tradingday.unique():
                    res_tmp = result[result.tradingday==tradingday_].copy()       
                    factor_data.append(FactorData(timestamp=tradingday_,
                                    factor_values=res_tmp))
            return factor_data
            
        except Exception as e:
            logger.error(f"加载因子数据失败: {e}")
            return []
            
    def get_all_asset_code(self,) -> str:
        """
        获取所有资产代码
        
        Args:
            category: 债券分类，如果为None则获取全部债券
            
        Returns:
            str: 逗号分隔的债券代码列表
        """
        try:
            with self.db.connection_context():
                # 构建基本查询
                query = (InInfIdpBndInfoDi
                    .select(InInfIdpBndInfoDi.tradingcode)
                    .join(
                        S07BndValuation,
                        JOIN.INNER,
                        on=(InInfIdpBndInfoDi.secucode == S07BndValuation.secucode))
                    .distinct())

                # 执行查询并转换为DataFrame
                result = pd.DataFrame(query.dicts())
            
            logger.info(f"条目记录数：{len(result)}")
            if result.empty:
                return ""
            return ','.join(result.tradingcode.tolist())
            
        except Exception as e:
            logger.error(f"获取所有资产代码失败: {e}")
            return ""
        
    def execute_query(self, sql):
        """
        执行通用SQL查询
        
        Args:
            sql: SQL查询语句
            
        Returns:
            pd.DataFrame: 查询结果
        """
        try:
            if isinstance(sql, str):
                # 对于原始SQL查询，保留原有的执行方式
                with self.engine.begin() as conn:
                    result = pd.read_sql(text(sql), conn)
                    self.engine.dispose()
                    return result
            else:
                # 对于已转换为Peewee Query对象的查询
                with self.db.connection_context():
                    return pd.DataFrame(query.dicts())
        except Exception as e:
            logger.error(f"执行查询失败: {e}")
            return pd.DataFrame()

    def get_trading_days(self, start: str, end: str) -> list:
        """
        获取交易日列表
        
        Args:
            start: 开始日期，格式YYYYMMDD
            end: 结束日期，格式YYYYMMDD
            
        Returns:
            list: 交易日列表
        """
        try:
            # 转换日期格式
            start_date = datetime.datetime.strptime(start, '%Y%m%d')
            end_date = datetime.datetime.strptime(end, '%Y%m%d')
            
            with self.db.connection_context():
                # 使用Peewee查询获取交易日
                query = (S07BndValuation
                    .select(S07BndValuation.tradingday)
                    .where(
                        (S07BndValuation.tradingday >= start_date) &
                        (S07BndValuation.tradingday <= end_date))
                    .group_by(S07BndValuation.tradingday)
                    .order_by(S07BndValuation.tradingday))
                
                # 获取结果
                trading_days = [day.tradingday for day in query]
            
            return trading_days
            
        except Exception as e:
            logger.error(f"获取交易日列表失败: {e}")
            return []


if __name__ == "__main__":
     # 读取配置文件
    db = MysqlDatabase()
    #  test = db.construct_asset('140024,160021')
    # test =  db.load_bar_data('140024,160021',
    #                         AssetType.BOND,   
    #                         OffsetType.DAY,
    #                         20210101,
    #                         20211231)
    # logger.info(test)
    #  logger.info(test[0].accrued_interest(datetime(2020,11,11,1,1,1),100))
    test = db.load_factor_data('019536,160008','20210101','20211231')
    logger.info(test)